package com.example.excelimporter.service;

import com.example.excelimporter.model.TableMeta;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.util.List;

@Service
public class TableMetaReader {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public TableMetaReader(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public TableMeta readTableMeta(String tableName) {
        TableMeta tableMeta = new TableMeta();
        tableMeta.setTableName(tableName);
        
        // 获取表注释
        String tableComment = jdbcTemplate.queryForObject(
                "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?",
                String.class, tableName);
        tableMeta.setTableComment(tableComment);

        // 获取列信息
        String sql = "SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT, IS_NULLABLE, COLUMN_KEY, CHARACTER_MAXIMUM_LENGTH " +
                "FROM information_schema.COLUMNS " +
                "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? " +
                "ORDER BY ORDINAL_POSITION";
        
        List<TableMeta.ColumnMeta> columns = jdbcTemplate.query(sql, (rs, rowNum) -> {
            TableMeta.ColumnMeta column = new TableMeta.ColumnMeta();
            column.setColumnName(rs.getString("COLUMN_NAME"));
            column.setColumnType(rs.getString("COLUMN_TYPE"));
            column.setColumnComment(rs.getString("COLUMN_COMMENT"));
            column.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
            column.setPrimaryKey("PRI".equals(rs.getString("COLUMN_KEY")));
            column.setMaxLength(rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
            return column;
        }, tableName);

        tableMeta.setColumns(columns);
        return tableMeta;
    }

    public List<String> getAllTables() {
        String sql = "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()";
        return jdbcTemplate.queryForList(sql, String.class);
    }
}
